package roomDao.roomImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import po.Room;
import roomDao.rDao;
import roomDao.Dbconnection.RoomDbconnection;

public class RoomImpl implements rDao{
		String sql = null;
		int Roomresult = 0;
	@Override
	public List<Room> findAll() {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		List<Room> roomList= new ArrayList<Room>();
		try {
			con = RoomDbconnection.getConnection();
			sql = "SELECT room_no,room_name FROM room";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) { 
				int roomNo = rs.getInt(1);
				String roomName = rs.getString(2);
		 	    Room room = new Room(roomNo,roomName);
			    roomList.add(room);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			RoomDbconnection.close(rs, ps);
		}
		return roomList;
	}

	@Override
	public void save(Room x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		try {
			con = RoomDbconnection.getConnection();
			sql = "INSERT INTO room VALUE(?,?)";
			ps = con.prepareStatement(sql);
			ps.setInt(1,x.getRoomNo());
			ps.setString(2, x.getRoomName());
//			ps.setInt(8, x.getRoom().getRoomNo());
//			ps.setString(9,  x.getRoom().getRoomName());
			Roomresult = ps.executeUpdate();
			System.out.println("操作结果是"+Roomresult);
		}catch(SQLException e) {
			e.printStackTrace();
		}finally{
			RoomDbconnection.close(rs, ps);
		}
		
	}

	@Override
	public void delete(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = RoomDbconnection.getConnection();
//            String str = x.replaceAll("[^\\d]+", "");//去掉非数字字符
//	        int roomNo = Integer.parseInt(str);
			sql = "DELETE room,student from room LEFT JOIN student ON room.room_no=student.room_no WHERE room_name=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			Roomresult = ps.executeUpdate();
			System.out.println("操作结果是"+Roomresult);
		}catch(SQLException e) {
			e.printStackTrace();
	}finally {
		RoomDbconnection.close(rs, ps);
	}
		
	}

	@Override
	public int get(int x) {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		int roomNo = 0;
		try {
			con = RoomDbconnection.getConnection();
			sql = "SELECT room_no FROM room WHERE room_no=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				roomNo = rs.getInt(1);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			RoomDbconnection.close(rs, ps);
		}
		return roomNo;
	}
	//查找宿舍 并返回房名
	@Override
	public String get(String x) {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		String roomName = null;
		try {
			con = RoomDbconnection.getConnection();
			sql = "SELECT room_name FROM room WHERE room_name=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				roomName = rs.getString(1);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			RoomDbconnection.close(rs, ps);
		}
		return roomName;
	}
	@Override
	public void update(Room x) {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		try {
			con = RoomDbconnection.getConnection();
			sql = "UPDATE room SET room_no=? WHERE room_name=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x.getRoomNo());
			ps.setString(2,x.getRoomName());
			Roomresult = ps.executeUpdate();
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			RoomDbconnection.close(rs, ps);
		}
	}

	@Override
	public Room getRoom(String x) {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		Room room = null;
		try {
			con = RoomDbconnection.getConnection();
			sql = "SELECT room_no,room_name FROM room WHERE room_name=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				int roomNo = rs.getInt(1);
				String roomName = rs.getString(2);
				room = new Room(roomNo,roomName);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			RoomDbconnection.close(rs, ps);
		}
		return room;
	}
	
}
